﻿using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.SceneManagement;
using UnityEngine.UI;
using Mono.Data.Sqlite;
using System.Data;
using System.Runtime.InteropServices;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using System;

public class ExportMgr : MonoBehaviour {
    public static ExportMgr s_Instance;
    public ExportUserPrefab m_ExportUserPrefab;
    public ExportExamPrefab m_ExportExamPrefab;
    public Transform m_UserContent, m_ExamContent;
    public List<ExportUserPrefab> m_UserList = new List<ExportUserPrefab>();
    public List<ExportExamPrefab> m_ExamList = new List<ExportExamPrefab>();
    public List<AnswerInfo> m_AnswerList = new List<AnswerInfo>();
    public Text m_ExportInfo;

    private ExportExamPrefab m_examPrefabNow;
    private ExportUserPrefab m_userPrefabNow;

    public class AnswerInfo
    {
        public string m_PaperName;
        public string m_UserId;
        public string m_OptionChoosed;
        public string m_Fillblank;
        public int m_Score;
        public int m_Type;
        public string m_Stem;
        public string m_Time;
    }

	
	void Awake () {
        s_Instance = this;
    }
    private void Start() {
        string cmd = "select distinct USER_ID from ANSWER_TABLE";
        SqliteDataReader reader = SQLManager.s_Instance.ExecuteSQLCommand(cmd);
        if (reader != null) {
            DataTable dataTable = new DataTable();
            dataTable.Load(reader);
            for (int i = 0; i < dataTable.Rows.Count; i++) {
                ExportUserPrefab user = Instantiate(m_ExportUserPrefab) as ExportUserPrefab;
                user.m_UserText.text = dataTable.Rows[i]["USER_ID"].ToString();
                user.transform.SetParent(m_UserContent);
                user.transform.localScale = new Vector3(1f, 1f, 1f);
                
                m_UserList.Add(user);
            }
            reader.Close();
        }
    }
    // Update is called once per frame
    void Update () {
		
	}
    public void OnClickUser(ExportUserPrefab user) {
        m_userPrefabNow = user;
        foreach (var item in m_UserList)
        {
            item.SetState(false);
        }
        user.SetState(true);

        for(int i =0; i < m_ExamContent.childCount; i++) {
            Destroy(m_ExamContent.GetChild(i).gameObject);
        }
        m_ExamList.Clear();
        string cmd = "select distinct TIME, PAPER_ID from ANSWER_TABLE WHERE USER_ID = '" + m_userPrefabNow.m_UserText.text+ "' ORDER BY PAPER_ID";
        SqliteDataReader reader = SQLManager.s_Instance.ExecuteSQLCommand(cmd);
        if (reader != null) {
            DataTable dataTable = new DataTable();
            dataTable.Load(reader);
            for (int i = 0; i < dataTable.Rows.Count; i++) {
                ExportExamPrefab exam = Instantiate(m_ExportExamPrefab) as ExportExamPrefab;
                exam.m_ExamText.text = dataTable.Rows[i]["PAPER_ID"].ToString();
                exam.m_TimeText.text = dataTable.Rows[i]["TIME"].ToString();
                exam.transform.SetParent(m_ExamContent);
                exam.transform.localScale = new Vector3(1f, 1f, 1f);
                
                m_ExamList.Add(exam);
            }
            reader.Close();
        }
    }
    public void OnClickExam(ExportExamPrefab exam) {
        m_examPrefabNow = exam;
        foreach (var item in m_ExamList) {
            item.SetState(false);
        }
        exam.SetState(true);
    }
    public void OnClcikExport() {
        if(m_examPrefabNow == null) {
            return;
        }
        OnClickChooseFile();
    }
    public void OnClickHome() {
        SceneManager.LoadScene("Scene");
    }
    public void OnClickChooseFile() {
        OpenFileName openFileName = new OpenFileName();

        openFileName.structSize = Marshal.SizeOf(openFileName);

        openFileName.filter = "Excel文件(*.xls)\0*.xls";

        openFileName.file = new string(new char[256]);

        openFileName.maxFile = openFileName.file.Length;

        openFileName.fileTitle = new string(new char[64]);

       // openFileName.filter = "图片文件(*.jpg*.png)\0*.jpg;*.png";

        openFileName.maxFileTitle = openFileName.fileTitle.Length;

        openFileName.initialDir = Application.streamingAssetsPath.Replace('/', '\\');//默认路径

        openFileName.title = "窗口标题";

        openFileName.flags = 0x00080000 | 0x00001000 | 0x00000800 | 0x00000008;

        if (LocalDialog.GetSaveFileName(openFileName)) {
            SaveData(openFileName.file,openFileName.fileTitle);
        }
    }
    void SaveData(string path,string name) {
        string cmd = "select * from ANSWER_TABLE where PAPER_ID = '"+m_examPrefabNow.m_ExamText.text+"' AND TIME ='"+ m_examPrefabNow.m_TimeText.text+"'";
        SqliteDataReader reader = SQLManager.s_Instance.ExecuteSQLCommand(cmd);
        if (reader != null) {
            DataTable dataTable = new DataTable();
            dataTable.Load(reader);
            for (int i = 0; i < dataTable.Rows.Count; i++) {
                AnswerInfo answerInfo = new AnswerInfo();

                answerInfo.m_UserId = dataTable.Rows[i]["USER_ID"].ToString();

                answerInfo.m_Fillblank = dataTable.Rows[i]["FILLBLANK"].ToString();
                answerInfo.m_Score = (int)dataTable.Rows[i]["SCORE"];
                answerInfo.m_Type = (int)dataTable.Rows[i]["TYPE"];
                answerInfo.m_Time = dataTable.Rows[i]["TIME"].ToString();
                string qcmd = "select Stem from QUESTION_TABLE where ID = "+ dataTable.Rows[i]["QID"].ToString();
                SqliteDataReader qreader = SQLManager.s_Instance.ExecuteSQLCommand(qcmd);
                DataTable qdataTable = new DataTable();
                qdataTable = new DataTable();
                qdataTable.Load(qreader);
                if (qdataTable.Rows.Count > 0) {
                    answerInfo.m_Stem = qdataTable.Rows[0]["Stem"].ToString();
                }

                string Pcmd = "select * from PAPER_TABLE where ID = " + dataTable.Rows[i]["PAPER_ID"].ToString();
                SqliteDataReader Preader = SQLManager.s_Instance.ExecuteSQLCommand(Pcmd);
                DataTable PdataTable = new DataTable();
                PdataTable = new DataTable();
                PdataTable.Load(Preader);
                if (PdataTable.Rows.Count > 0) {
                    answerInfo.m_PaperName = PdataTable.Rows[0]["Name"].ToString();
                }
                string opt = dataTable.Rows[i]["OPT_CHOSED"].ToString();
                string[] optlist = opt.Split('_');
                if (opt.Length>0&&optlist.Length > 0) {
                    foreach (var item in optlist) {
                        string Ocmd = "select Stem from OPTION_TABLE where ID = " + item;
                        SqliteDataReader Oreader = SQLManager.s_Instance.ExecuteSQLCommand(Ocmd);
                        DataTable OdataTable = new DataTable();
                        OdataTable = new DataTable();
                        OdataTable.Load(Oreader);
                        if (OdataTable.Rows.Count > 0) {
                            answerInfo.m_OptionChoosed += OdataTable.Rows[0]["Stem"] + "；";
                        }
                    }
                }
               

                m_AnswerList.Add(answerInfo);
            }
            reader.Close();
        }
        print(path);
        if (path.Contains(".xls")) {
            path = path.Remove(path.Length - 4);
        }
        try {
            FileStream MyAddress = new FileStream(path + ".xls", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
            HSSFWorkbook MyWorkbook = new HSSFWorkbook();

            HSSFSheet Sheet01 = (HSSFSheet)MyWorkbook.CreateSheet(name);
            HSSFRow Row0 = (HSSFRow)Sheet01.CreateRow((short)0);//表头
            for (int i = 0; i < 8; i++) {
                List<HSSFCell> cellList = new List<HSSFCell>();
                for (int j = 0; j < 8; j++) {
                    HSSFCell cell = (HSSFCell)Row0.CreateCell((short)j);
                    cellList.Add(cell);
                }
                cellList[0].SetCellValue("试卷名");
                cellList[1].SetCellValue("用户名");
                cellList[2].SetCellValue("选择的选项");
                cellList[3].SetCellValue("填空内容");
                cellList[4].SetCellValue("得分");
                cellList[5].SetCellValue("题目类型");
                cellList[6].SetCellValue("题干");
                cellList[7].SetCellValue("答题时间");
                HSSFFont MyFont = (HSSFFont)MyWorkbook.CreateFont();
                MyFont.Color = HSSFColor.Red.Index;
                MyFont.Boldweight = (short)FontBoldWeight.Bold;
                cellList[0].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[0].CellStyle.SetFont(MyFont);
                cellList[1].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[1].CellStyle.SetFont(MyFont);
                cellList[2].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[2].CellStyle.SetFont(MyFont);
                cellList[3].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[3].CellStyle.SetFont(MyFont);
                cellList[4].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[4].CellStyle.SetFont(MyFont);
                cellList[5].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[5].CellStyle.SetFont(MyFont);
                cellList[6].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[6].CellStyle.SetFont(MyFont);
                cellList[7].CellStyle = MyWorkbook.CreateCellStyle();
                cellList[7].CellStyle.SetFont(MyFont);
            }
            int scoreAll = 0;
            int i1 = 0;
            for (; i1 < m_AnswerList.Count; i1++) {
                //print(i);
                HSSFRow Row = (HSSFRow)Sheet01.CreateRow((short)i1+1);
                List<HSSFCell> cellList = new List<HSSFCell>();
                for (int j = 0; j < 8; j++) {
                    HSSFCell cell = (HSSFCell)Row.CreateCell((short)j);
                    cellList.Add(cell);
                }
                cellList[0].SetCellValue(m_AnswerList[i1].m_PaperName.ToString());
                cellList[1].SetCellValue(m_AnswerList[i1].m_UserId);
                cellList[2].SetCellValue(m_AnswerList[i1].m_OptionChoosed);
                cellList[3].SetCellValue(m_AnswerList[i1].m_Fillblank);
                cellList[4].SetCellValue(m_AnswerList[i1].m_Score.ToString());
                scoreAll += (int)m_AnswerList[i1].m_Score;
                switch (m_AnswerList[i1].m_Type) {
                    case 1:
                        cellList[5].SetCellValue("单选题");
                        break;
                    case 2:
                        cellList[5].SetCellValue("多选题");
                        break;
                    case 3:
                        cellList[5].SetCellValue("填空");
                        break;
                }

                cellList[6].SetCellValue(m_AnswerList[i1].m_Stem);
                cellList[7].SetCellValue(m_AnswerList[i1].m_Time);
            }
            HSSFRow RowScoreAll = (HSSFRow)Sheet01.CreateRow((short)i1 + 1);
            HSSFCell cellScoreAll = (HSSFCell)RowScoreAll.CreateCell((short)4);
            HSSFRow RowScore = (HSSFRow)Sheet01.CreateRow((short)i1 + 2);
            HSSFCell cellScore = (HSSFCell)RowScore.CreateCell((short)4);
            cellScoreAll.SetCellValue("总得分");
            cellScore.SetCellValue(scoreAll);
            HSSFFont MyFont1 = (HSSFFont)MyWorkbook.CreateFont();
            MyFont1.Color = HSSFColor.Red.Index;
            MyFont1.Boldweight = (short)FontBoldWeight.Bold;
            cellScoreAll.CellStyle = MyWorkbook.CreateCellStyle();
            cellScoreAll.CellStyle.SetFont(MyFont1);


            MyWorkbook.Write(MyAddress);
            MyWorkbook.Close();
            MyAddress.Close();
            m_AnswerList.Clear();
            m_ExportInfo.text = "导出成功";
        }
        catch(Exception e) {
            print(e);
            m_ExportInfo.text = "导出失败";
        }
       
    }
}
